package cn.com.libertymutual.sp.dao;

import java.util.List;

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import cn.com.libertymutual.sp.bean.TbSpScoreLogOut;

@Repository
public interface ScoreLogOutDao extends PagingAndSortingRepository<TbSpScoreLogOut, Integer>, JpaSpecificationExecutor<TbSpScoreLogOut> {

	// @Query("select COALESCE(sum(t.acChangeScore),0) from TbSpScoreLogOut t where
	// t.userCode=?1 and t.changeType=?2 and t.status=1")
	// int findByUCodeAndType(String userCode, String changeType);
	@Query("select t from TbSpScoreLogOut t where t.userCode=?1 and t.changeType=?2 and t.status=1")
	List<TbSpScoreLogOut> findByUCodeAndType(String userCode, String changeType);

	@Query(value = "select t1.id,2 as type,t1.change_type,t1.re_changescore,t1.ac_changescore,t1.reason,t1.reason_no,t1.change_time,t1.remark from tb_sp_scorelog_out as t1 where t1.user_code = :userCode and t1.status =1 order by t1.change_time desc limit :pageNumber , :pageSize", nativeQuery = true)
	List<String> findAllOut(@Param("userCode") String userCode, @Param("pageNumber") int pageNumber, @Param("pageSize") int pageSize);

	// 最近一天到期的总额
	@Query(value = "select SUM(ac_changescore) as score,effictive_date as date from tb_sp_scorelog_out where user_code = ?1 AND change_type=6 and effictive_date =(select effictive_date from tb_sp_scorelog_out where user_code=?1 and change_type='6' ORDER BY effictive_date desc limit 1) GROUP BY effictive_date", nativeQuery = true)
	List<String> findExpireByUCode(String userCode);

	// 修改用户编码
	@Transactional
	@Modifying(clearAutomatically = true)
	@Query("update TbSpScoreLogOut set userCode = :userCode, telephone = :telephone where userCode = :userCode1 and telephone = :telephone1")
	public int updateUserCodeByUserCode(@Param("userCode") String userCode, @Param("telephone") String telephone,
			@Param("userCode1") String userCode1, @Param("telephone1") String telephone1);
}
